using System;
using System.Data;
using System.Configuration;
using System.Data.Sql;
using System.Data.SqlClient;
using WorldGoldenPrice;
using SJCGoldenPrice;
using WorldIndexInfo;
using System.Configuration;
namespace DatabaseService
{
public class AccessDB
{

    private static SqlConnection getConnect()
    {
        return new SqlConnection(ConfigurationSettings.AppSettings["ConnectString"].Trim());
    }

   

    public static bool InsertGoldenPriceDB(GoldenPrice_Factory obj, string Provider)
    {
        bool bResult=false;
        SqlConnection l_SQLConnection=getConnect();
          try
            {
                l_SQLConnection.Open();
                //string strSQL = "insert into [MarketInfo.Product](WorldGoldBuy,WorldGoldSell,VnGoldBuy,VnGoldSell,OilBuy,OilSell,Provider,DateModified,EURUSD, GBPUSD, USDJPY, USDCAD, AUDUSD, NZDUSD,USDNOK) values(@WorldBuy,@WorldSell,@VnBuy,@VnSell,@OilBuy,@OilSell,@Provider,@DateModified,@EURUSD, @GBPUSD, @USDJPY, @USDCAD, @AUDUSD, @NZDUSD,@USDNOK) ";
                string strSQL = "insert into [MarketInfo.Product](WorldGoldBuy,WorldGoldSell,VnGoldBuy,VnGoldSell,OilBuy,OilSell,Provider,DateModified) values(@WorldBuy,@WorldSell,@VnBuy,@VnSell,@OilBuy,@OilSell,@Provider,@DateModified) ";

                    SqlCommand cmd = new SqlCommand(strSQL, l_SQLConnection);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("@WorldBuy",obj.World_Buy);
                    cmd.Parameters.AddWithValue("@WorldSell", obj.World_Sell);
                    cmd.Parameters.AddWithValue("@VnBuy", obj.Vn_Buy);
                    cmd.Parameters.AddWithValue("@VnSell", obj.Vn_Sell);
                    cmd.Parameters.AddWithValue("@OilBuy", obj.OilBuy);
                    cmd.Parameters.AddWithValue("@OilSell", obj.OilSell);
                    cmd.Parameters.AddWithValue("@Provider", Provider);
                    cmd.Parameters.AddWithValue("@DateModified", DateTime.Now);
                    //cmd.Parameters.AddWithValue("@EURUSD", obj.EURUSD);
                    //cmd.Parameters.AddWithValue("@GBPUSD", obj.GBPUSD);
                    //cmd.Parameters.AddWithValue("@USDJPY", obj.USDJPY);
                    //cmd.Parameters.AddWithValue("@USDCAD", obj.USDCAD);
                    //cmd.Parameters.AddWithValue("@AUDUSD", obj.AUDUSD);
                    //cmd.Parameters.AddWithValue("@NZDUSD", " ");
                    //cmd.Parameters.AddWithValue("@USDNOK", "");
                   if( cmd.ExecuteNonQuery()>0)
                          bResult=false;
                             
            }
            catch (Exception ex)
            {
                TLog.WriteErr("DataAccess : InsertGoldenPriceDB", ex.ToString());
            }
            finally
            {

                if (l_SQLConnection != null && l_SQLConnection.State == ConnectionState.Open)
                    l_SQLConnection.Close();
            }
            return bResult;
        }

  public static bool InsertSJCPriceDB(SJC_Factory obj, string Provider)
    {
        bool bResult = false;
        SqlConnection l_SQLConnection = getConnect();
        try
        {
            l_SQLConnection.Open();
            string strSQL = "insert into [MarketInfo.SJC](HaNoiBuy,HaNoiSell,HcmBuy,HcmSell,DaNangBuy,DaNangSell,NhaTrangBuy,NhaTrangSell,CanThoBuy,CanThoSell, Provider, DateModified) values(@HaNoiBuy,@HaNoiSell,@HcmBuy,@HcmSell,@DaNangBuy,@DaNangSell,@NhaTrangBuy,@NhaTrangSell,@CanThoBuy,@CanThoSell, @Provider, @DateModified) ";

            SqlCommand cmd = new SqlCommand(strSQL, l_SQLConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@HaNoiBuy", obj.Ha_noi.Mua);
            cmd.Parameters.AddWithValue("@HaNoiSell", obj.Ha_noi.Ban);
            cmd.Parameters.AddWithValue("@HcmBuy", obj.Hcm.Mua);
            cmd.Parameters.AddWithValue("@HcmSell", obj.Hcm.Ban);
            cmd.Parameters.AddWithValue("@DaNangBuy", obj.Da_nang.Mua);
            cmd.Parameters.AddWithValue("@DaNangSell", obj.Da_nang.Ban);
            cmd.Parameters.AddWithValue("@CanThoBuy", obj.Can_tho.Mua);
            cmd.Parameters.AddWithValue("@CanThoSell", obj.Can_tho.Ban);
            cmd.Parameters.AddWithValue("@NhaTrangBuy", obj.Nha_trang.Mua);
            cmd.Parameters.AddWithValue("@NhaTrangSell", obj.Nha_trang.Ban);
            cmd.Parameters.AddWithValue("@Provider", Provider);
            cmd.Parameters.AddWithValue("@DateModified", DateTime.Now);
            
           
            if (cmd.ExecuteNonQuery() > 0)
                bResult = false;

        }
        catch (Exception ex)
        {
            TLog.WriteErr("DataAccess : InsertSJCPriceDB", ex.ToString());
        }
        finally
        {

            if (l_SQLConnection != null && l_SQLConnection.State == ConnectionState.Open)
                l_SQLConnection.Close();
        }
        return bResult;
    }
    public static bool InsertExchangeDB(WorldExchange.Exchange_Factory obj, string Provider)
    { 
          bool bResult=false;
        SqlConnection l_SQLConnection=getConnect();
          try
            {
                l_SQLConnection.Open();
              
               // string strSQL = "insert into tbl_Exchange(USD_buy,USD_buytransfer,USD_sell,EUR_buy,EUR_buytransfer,EUR_sell,GBP_buy,GBP_buytransfer,GBP_sell,AUD_buy,AUD_buytransfer,AUD_sell,CHF_buy,CHF_buytransfer,CHF_sell,JPY_buy,JPY_buytransfer,JPY_sell,SGD_buy,SGD_buytransfer,SGD_sell,Date_modified,Provider) ";
                string strSQL = "insert into [MarketInfo.Exchange](USDBuy,USDBuyTransfer,USDSell,EURBuy,EURBuyTransfer,EURSell,GBPBuy,GBPBuyTransfer,GBPSell,AUDBuy,AUDBuyTransfer,AUDSell,CHFBuy,CHFBuyTransfer,CHFSell,JPYBuy,JPYBuyTransfer,JPYSell,SGDBuy,SGDBuyTransfer,SGDSell,DateModified,Provider) ";
                strSQL += " values(@USD_buy,@USD_buytransfer,@USD_sell,@EUR_buy,@EUR_buytransfer,@EUR_sell,@GBP_buy,@GBP_buytransfer,@GBP_sell,@AUD_buy,@AUD_buytransfer,@AUD_sell,@CHF_buy,@CHF_buytransfer,@CHF_sell,@JPY_buy,@JPY_buytransfer,@JPY_sell,@SGD_buy,@SGD_buytransfer,@SGD_sell,@Date_modified,@Provider)";

                    SqlCommand cmd = new SqlCommand(strSQL, l_SQLConnection);
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@USD_sell",obj.USD.Ban);
                    cmd.Parameters.AddWithValue("@USD_buy",obj.USD.Mua_TM);
                    cmd.Parameters.AddWithValue("@USD_buytransfer",obj.USD.Mua_CK);
                    
                    cmd.Parameters.AddWithValue("@EUR_sell",obj.EUR.Ban);
                    cmd.Parameters.AddWithValue("@EUR_buy",obj.EUR.Mua_TM);
                    cmd.Parameters.AddWithValue("@EUR_buytransfer",obj.EUR.Mua_CK);

                    cmd.Parameters.AddWithValue("@GBP_sell",obj.GBP.Ban);
                    cmd.Parameters.AddWithValue("@GBP_buy",obj.GBP.Mua_TM);
                    cmd.Parameters.AddWithValue("@GBP_buytransfer",obj.GBP.Mua_CK);
                    
                    cmd.Parameters.AddWithValue("@AUD_sell",obj.AUD.Ban);
                    cmd.Parameters.AddWithValue("@AUD_buy",obj.AUD.Mua_TM);
                    cmd.Parameters.AddWithValue("@AUD_buytransfer",obj.AUD.Mua_CK);
                    
                    cmd.Parameters.AddWithValue("@CHF_sell",obj.CHF.Ban);
                    cmd.Parameters.AddWithValue("@CHF_buy",obj.CHF.Mua_TM);
                    cmd.Parameters.AddWithValue("@CHF_buytransfer",obj.CHF.Mua_CK);

                    cmd.Parameters.AddWithValue("@JPY_sell",obj.JPY.Ban);
                    cmd.Parameters.AddWithValue("@JPY_buy",obj.JPY.Mua_TM);
                    cmd.Parameters.AddWithValue("@JPY_buytransfer",obj.JPY.Mua_CK);

                    cmd.Parameters.AddWithValue("@SGD_sell", obj.SGD.Ban);
                    cmd.Parameters.AddWithValue("@SGD_buy", obj.SGD.Mua_TM);
                    cmd.Parameters.AddWithValue("@SGD_buytransfer", obj.SGD.Mua_CK);


                    cmd.Parameters.AddWithValue("@Provider", Provider);
                    cmd.Parameters.AddWithValue("@Date_modified", DateTime.Now);
                   if( cmd.ExecuteNonQuery()>0)

                       bResult=false;


                
            }
            catch (Exception ex)
            {
                //throw (ex);
                TLog.WriteErr("DataAccess : InsertExchangeDB", ex.ToString());
            }
            finally
            {
                if (l_SQLConnection != null && l_SQLConnection.State == ConnectionState.Open)
                    l_SQLConnection.Close();
            }
            return bResult;
        }
    public static WorldExchange.Exchange_Factory getExchangeInfo()
    {
        DataTable dt=new DataTable();;
        WorldExchange.Exchange_Factory objResult = new WorldExchange.Exchange_Factory();
        SqlConnection l_SQLConnection = getConnect();
        try
        {
            l_SQLConnection.Open();
            string strSQL = "select top 1 * from [MarketInfo.Exchange] order  by ExchangeID DESC  ";
           SqlDataAdapter adap = new SqlDataAdapter(strSQL, l_SQLConnection);
                adap.Fill(dt);
                    
        }
        catch (Exception ex)
        {
            TLog.WriteErr("DataAccess : getExchangeInfo", ex.ToString());
        }
        finally {
            if (l_SQLConnection != null && l_SQLConnection.State == ConnectionState.Open)
                l_SQLConnection.Close();
        }
        if (dt.Rows.Count > 0)
        {
            objResult.USD = SetValue(dt.Rows[0]["USDSell"], dt.Rows[0]["USDBuy"], dt.Rows[0]["USDBuyTransfer"]);
            objResult.EUR = SetValue(dt.Rows[0]["EURSell"], dt.Rows[0]["EURBuy"], dt.Rows[0]["EURBuyTransfer"]);
            objResult.GBP = SetValue(dt.Rows[0]["GBPSell"], dt.Rows[0]["GBPBuy"], dt.Rows[0]["GBPBuyTransfer"]);
            objResult.AUD = SetValue(dt.Rows[0]["AUDSell"], dt.Rows[0]["AUDBuy"], dt.Rows[0]["AUDBuyTransfer"]);
            objResult.CHF = SetValue(dt.Rows[0]["CHFSell"], dt.Rows[0]["CHFBuy"], dt.Rows[0]["CHFBuyTransfer"]);
            objResult.JPY = SetValue(dt.Rows[0]["JPYSell"], dt.Rows[0]["JPYBuy"], dt.Rows[0]["JPYBuyTransfer"]);
            objResult.SGD = SetValue(dt.Rows[0]["SGDSell"], dt.Rows[0]["SGDBuy"], dt.Rows[0]["SGDBuyTransfer"]);
            //objResult.USD = SetValue(dt.Rows[0]["USD_sell"], dt.Rows[0]["USD_buy"], dt.Rows[0]["USD_buytransfer"]);
        }

        return objResult;
      
    }

    public static GoldenPrice_Factory getProductInfo()
    {
        DataTable dt = new DataTable(); ;
        GoldenPrice_Factory objResult = new GoldenPrice_Factory();
        SqlConnection l_SQLConnection = getConnect();
        try
        {
            l_SQLConnection.Open();
            string strSQL = "select top 1 * from [MarketInfo.Product] order  by ProductID DESC  ";
            SqlDataAdapter adap = new SqlDataAdapter(strSQL, l_SQLConnection);
            adap.Fill(dt);

        }
        catch (Exception ex)
        {
            TLog.WriteErr("DataAccess : getProductInfo", ex.ToString());
        }
        finally {
            if (l_SQLConnection != null && l_SQLConnection.State == ConnectionState.Open)    
                l_SQLConnection.Close();
        }
        if (dt.Rows.Count > 0)
        {
            if (dt.Rows[0]["OilBuy"] != DBNull.Value)
                objResult.OilBuy = dt.Rows[0]["OilBuy"].ToString().Trim();
            if (dt.Rows[0]["OilSell"] != DBNull.Value)
                objResult.OilSell = dt.Rows[0]["OilSell"].ToString().Trim();
            if (dt.Rows[0]["EURUSD"] != DBNull.Value)
                objResult.EURUSD = dt.Rows[0]["EURUSD"].ToString().Trim();
            if (dt.Rows[0]["GBPUSD"] != DBNull.Value)
                objResult.GBPUSD = dt.Rows[0]["GBPUSD"].ToString().Trim();
            if (dt.Rows[0]["USDJPY"] != DBNull.Value)
                objResult.USDJPY = dt.Rows[0]["USDJPY"].ToString().Trim();
            if (dt.Rows[0]["USDCAD"] != DBNull.Value)
                objResult.USDCAD = dt.Rows[0]["USDCAD"].ToString().Trim();
            if (dt.Rows[0]["AUDUSD"] != DBNull.Value)
                objResult.AUDUSD = dt.Rows[0]["AUDUSD"].ToString().Trim();
            if (dt.Rows[0]["NZDUSD"] != DBNull.Value)
                objResult.NZDUSD = dt.Rows[0]["NZDUSD"].ToString().Trim();
            if (dt.Rows[0]["USDNOK"] != DBNull.Value)
                objResult.USDNOK = dt.Rows[0]["USDNOK"].ToString().Trim();
              
           
        }

        return objResult;

    }

    public static WorldExchange.Price SetValue(object Sell, object Buy, object Tran)
    {
        WorldExchange.Price objResult= new WorldExchange.Price();
        if (Sell != DBNull.Value)
            objResult.Ban = Sell.ToString();
        if (Buy != DBNull.Value)
            objResult.Mua_TM = Buy.ToString();
        if (Tran != DBNull.Value)
            objResult.Mua_CK = Tran.ToString();
        return objResult;

    }

    public static bool InsertWorldIndexDB(WorldIndexInfo.WorldIndex_Factory obj, string Provider)
    {
        return true;
    }
    //    bool bResult = false;
    //    SqlConnection l_SQLConnection = getConnect();
    //    try
    //    {
    //        l_SQLConnection.Open();

    //        // string strSQL = "insert into tbl_Exchange(USD_buy,USD_buytransfer,USD_sell,EUR_buy,EUR_buytransfer,EUR_sell,GBP_buy,GBP_buytransfer,GBP_sell,AUD_buy,AUD_buytransfer,AUD_sell,CHF_buy,CHF_buytransfer,CHF_sell,JPY_buy,JPY_buytransfer,JPY_sell,SGD_buy,SGD_buytransfer,SGD_sell,Date_modified,Provider) ";
    //        string strSQL = "insert into [MarketInfo.WorldIndex](DJIAPrice,DJIAChange,NasdaqPrice,NasdaqChange,FTSE100Price,FTSE100Change,NikkeiPrice,NikkeiChange,HSengPrice,HSengChange,DateModified,Provider) ";
    //        strSQL += " values(@DJIA_Price,@DJIA_Change,@Nasdaq_Price,@Nasdaq_Change,@FTSE100_Price,@FTSE100_Change,@Nikkei_Price,@Nikkei_Change,@HSeng_Price,@HSeng_Change,@Date_modified,@Provider)";

    //        SqlCommand cmd = new SqlCommand(strSQL, l_SQLConnection);
    //        cmd.CommandType = CommandType.Text;

    //        cmd.Parameters.AddWithValue("@DJIA_Price", obj.DJIAinfo.Value);
    //        cmd.Parameters.AddWithValue("@DJIA_Change", obj.DJIAinfo.Change);


    //        cmd.Parameters.AddWithValue("@Nasdaq_Price", obj.Nadaqinfo.Value);
    //        cmd.Parameters.AddWithValue("@Nasdaq_Change", obj.Nadaqinfo.Change);


    //        cmd.Parameters.AddWithValue("@FTSE100_Price", obj.Londoninfo.Value);
    //        cmd.Parameters.AddWithValue("@FTSE100_Change", obj.Londoninfo.Change);


    //        cmd.Parameters.AddWithValue("@Nikkei_Price", obj.Nikkei225info.Value);
    //        cmd.Parameters.AddWithValue("@Nikkei_Change", obj.Nikkei225info.Change);


    //        cmd.Parameters.AddWithValue("@HSeng_Price", obj.Hongkonginfo.Value);
    //        cmd.Parameters.AddWithValue("@HSeng_Change", obj.Hongkonginfo.Change);

    //        cmd.Parameters.AddWithValue("@Provider", Provider);
    //        cmd.Parameters.AddWithValue("@Date_modified", DateTime.Now);
    //        if (cmd.ExecuteNonQuery() > 0)
    //              bResult = false;
    //    }
    //    catch (Exception ex)
    //    {
    //        TLog.WriteErr("DataAccess : InsertWorldIndexDB", ex.ToString());
    //    }
    //    finally
    //    {
    //        if (l_SQLConnection != null && l_SQLConnection.State == ConnectionState.Open)
    //            l_SQLConnection.Close();
    //    }
    //    return bResult;
    //}

    public static void UpdateVGBProfile()
    {
    
      SqlConnection l_SQLConnection=null;// getConnect();
        try
        {
            l_SQLConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectVGBProfile"].Trim());
            l_SQLConnection.Open();
            string strSQL = "Update ExchangeProcessStatus Set LastTimeProcess = GetDate(), LastServerProcess = @ServerName Where Service = @Service";
            SqlCommand cmd = new SqlCommand(strSQL, l_SQLConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@Service", "MarketInfo");
            cmd.Parameters.AddWithValue("@ServerName", Environment.MachineName.Trim());
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            TLog.WriteErr("DataAccess : UpdateVGBProfile", ex.ToString());
        }

        finally
        {
            if (l_SQLConnection != null && l_SQLConnection.State == ConnectionState.Open)
                l_SQLConnection.Close();
        }
    }
    /// <summary>
    /// 1 la duoc phep ghi,coi nhu cung server va  Now-lastupdate< ProductTimer
    /// 2 la duoc phep ghi khi khac server  va Now-lastupdate>ProductTimer
    /// 0 la ko duoc phep
    /// </summary>
    /// <returns></returns>
    public static int CheckRoleWrite()
    { 
        string LastServerProcess=null;
        //string CurrentServerProcess = Environment.MachineName;
        DateTime LastTimeProcess ;
        //DateTime CurrentTimeProcess = DateTime.Now;
        int ProductTimer=int.Parse(ConfigurationSettings.AppSettings["ProductTimer"].Trim());
        GetExchangeProcessStatus(out LastServerProcess, out LastTimeProcess);
        if (LastServerProcess == null || LastTimeProcess == null)
            return 1;
        if( LastTimeProcess.Add(new TimeSpan(0,0,(ProductTimer+1)))< DateTime.Now)
            return 2;
       
        if((LastTimeProcess.Add(new TimeSpan(0,0,(ProductTimer+1)))>=DateTime.Now)&&(LastServerProcess.Trim() == Environment.MachineName.Trim()))
                return 1;
       
        return 0;
       
    }

    public static void GetExchangeProcessStatus(out string LastServerProcess, out DateTime LastTimeProcess)
    {

        LastServerProcess = "";
        LastTimeProcess = DateTime.Now.AddDays(-1);
        SqlConnection l_SQLConnection =null;
        try {
            l_SQLConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectVGBProfile"].Trim());
            DataTable dt = new DataTable();
            l_SQLConnection.Open();
            string strSQL = "select top 1 * from [ExchangeProcessStatus] where Service='{0}'";
            strSQL = string.Format(strSQL, "MarketInfo");
            SqlDataAdapter adap = new SqlDataAdapter(strSQL, l_SQLConnection);
            adap.Fill(dt);

            if (dt.Rows.Count > 0)
            {
                if (dt.Rows[0]["LastTimeProcess"] != DBNull.Value)
                    LastTimeProcess = DateTime.Parse( dt.Rows[0]["LastTimeProcess"].ToString().Trim());
                if (dt.Rows[0]["LastServerProcess"] != DBNull.Value)
                    LastServerProcess = dt.Rows[0]["LastServerProcess"].ToString().Trim();
             }
            


        }
        catch (Exception ex)
        {
            TLog.WriteErr("GetExchangeProcessStatus:",ex.ToString());
        }
        finally
        {
            if (l_SQLConnection != null && l_SQLConnection.State == ConnectionState.Open)
                l_SQLConnection.Close(); 
        }

       // return bResult;

    }

 }
}